import pandas as pd
import matplotlib.pyplot as plt
import numpy as np
import matplotlib as mpl
import sqlalchemy

# 创建数据库连接
engine = sqlalchemy.create_engine('mysql+pymysql://username:password@localhost/database_name')

# 从auction_assets表获取数据
query = """
SELECT 
    a.id AS auction_id,
    a.bond_code,
    a.start_price,
    b.bid_price AS market_price,
    b.bid_time AS created_time
FROM 
    auction_assets a
JOIN 
    auction_bids b ON a.id = b.auction_id
WHERE 
    a.bond_code = %(bond_code)s
ORDER BY 
    b.bid_time
"""

# 选择特定债券代码（示例中使用表中第6个债券）
bond_code_query = "SELECT bond_code FROM auction_assets LIMIT 1 OFFSET 5;"
bond_code = pd.read_sql(bond_code_query, engine).iloc[0,0]

# 获取该债券的所有投标数据
df = pd.read_sql(query, engine, params={'bond_code': bond_code})

# 数据处理
cusip_time = df[['market_price', 'created_time']]
cusip_time.reset_index(drop=True, inplace=True)
cusip_time.created_time = cusip_time.created_time.astype(str)

# 计算峰值和谷值
data = cusip_time['market_price'].values
double_diff = np.diff(np.sign(np.diff(data)))
peak_locations = np.where(double_diff == -2)[0] + 1
double_diff2 = np.diff(np.sign(np.diff(-1 * data)))
trough_locations = np.where(double_diff2 == -2)[0] + 1

# 绘制图表
plt.figure(figsize=(16, 10), dpi=80)
plt.plot('created_time', 'market_price', data=cusip_time, color='tab:blue', label='Bid Price')
plt.scatter(cusip_time.created_time[peak_locations], cusip_time.market_price[peak_locations],
            marker=mpl.markers.CARETUPBASE, color='tab:green', s=100, label='Peaks')
plt.scatter(cusip_time.created_time[trough_locations], cusip_time.market_price[trough_locations],
            marker=mpl.markers.CARETDOWNBASE, color='tab:red', s=100, label='Troughs')

# 标注
for t, p in zip(trough_locations[1::5], peak_locations[::5]):
    plt.text(cusip_time.created_time[p], cusip_time.market_price[p] + 0.5,
             pd.to_datetime(cusip_time.created_time[p]).strftime('%H:%M'),
             horizontalalignment='center', color='darkgreen')
    plt.text(cusip_time.created_time[t], cusip_time.market_price[t] - 0.5,
             pd.to_datetime(cusip_time.created_time[t]).strftime('%H:%M'),
             horizontalalignment='center', color='darkred')

# 图表装饰
xtick_location = cusip_time.index.tolist()[::5]
xtick_labels = [pd.to_datetime(x).strftime('%H:%M') for x in cusip_time.created_time.tolist()[::5]]
plt.xticks(ticks=xtick_location, labels=xtick_labels, rotation=90, fontsize=12, alpha=.7)
plt.title(f"Peak and Troughs of BWIC Auction Price - Bond: {bond_code}", fontsize=18)
plt.yticks(fontsize=12, alpha=.7)

# 美化边框
plt.gca().spines["top"].set_alpha(.0)
plt.gca().spines["bottom"].set_alpha(.3)
plt.gca().spines["right"].set_alpha(.0)
plt.gca().spines["left"].set_alpha(.3)
plt.legend(loc='upper left')
plt.grid(axis='y', alpha=.3)
plt.tight_layout()
plt.show()